#!/usr/bin/env python
#coding=utf-8

import json
import pandas as pd
import numpy as np
from pprint import pprint
from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkbssopenapi.request.v20171214.DescribeInstanceBillRequest import DescribeInstanceBillRequest
from core.logger import logger


class AliyunBillUtils(object):
    def __init__(self, company_name, accessKeyId, accessSecret, RegionId='cn-shenzhen'):
        self.client = AcsClient(accessKeyId, accessSecret, RegionId)
        self.company_name = company_name
        self.company_account_info = {
            "welab": {
                "1480048852793114": "sa",
                "1591864373775362": "welabfintech",
                "1492906811569100": "welabconsfin"
            },
            "tianmian": {
                "1409166372891082": "tianmian",
                "1458598865203590": "tianmianlab",
                "1741897409018435": "welabbank"
            }
        }


    def get_bill_by_account(self, billing_cycle, bill_owner_id):
        """
        根据账号id，获取账单
        """
        max_results = 300
        next_token = ""
        bill_info = []

        while True:
            request = DescribeInstanceBillRequest()
            request.set_accept_format('json')
            request.set_BillingCycle(billing_cycle)
            request.set_BillOwnerId(bill_owner_id)
            request.set_MaxResults(max_results)
            request.set_NextToken(next_token)
            request.set_IsBillingItem(True)

            try:
                response = self.client.do_action_with_exception(request)
                response = json.loads(str(response, encoding='utf-8'))
                bill_info.extend(response["Data"]["Items"])
                if "NextToken" not in response["Data"]:
                    break
                else:
                    next_token = response["Data"]["NextToken"]
            except Exception as e:
                logger.exception(f"get_bill_by_account:{bill_owner_id} error =>{e}")
                return False

        return bill_info

    def get_bill_by_company(self, billing_cycle):
        """
        获取公司主体的所有账单
        """
        if self.company_name not in self.company_account_info.keys():
            logger.error(f"get_bill_by_company failed, {self.company_name} not available")
            return False

        bill_info = []
        # 遍历公司的所有账号
        for account_id, account_name in self.company_account_info[self.company_name].items():
            bill = self.get_bill_by_account(billing_cycle, account_id)
            if not bill:
                logger.error(f"get bill by {account_name} failed")
                continue

            bill_info.extend(bill)

        return bill_info


    def get_bill_to_database(self, cycle):
        """
        获取公司账单，返回数据库格式
        """
        data = []
        SubscriptionType = {
            "PayAsYouGo": "后付费",
            "Subscription": "预付费",
        }
        Item = {
            "Refund": "退款",
            "PayAsYouGoBill": "后付费账单",
            "SubscriptionOrder": "预付账单",
            "Adjustment": "调账"
        }
        # 获取公司所有的账单
        bill_infos = self.get_bill_by_company(cycle)
        for bill_info in bill_infos:
            data_tmp = {
                "bill_cycle": cycle,
                "cloud_platform": "阿里云",
                "cost_unit": bill_info["CostUnit"],
                "account_id": bill_info["OwnerID"],
                "account_name": self.company_account_info[self.company_name][bill_info["OwnerID"]],
                "owner_id": bill_info["OwnerID"],
                "product_code": bill_info["ProductCode"],
                "product_name": bill_info["ProductName"],
                "commodity_code": bill_info["CommodityCode"],
                "product_detail": bill_info["ProductDetail"],
                "subscription_type": SubscriptionType[bill_info["SubscriptionType"]],
                "service_period": bill_info["ServicePeriod"],
                "service_period_unit": bill_info["ServicePeriodUnit"],
                "bill_type": Item[bill_info["Item"]] ,
                "bill_method": bill_info["BillingType"],
                "bill_item": bill_info["BillingItem"],
                "instance_id": bill_info["InstanceID"],
                "instance_name": bill_info["NickName"],
                "resource_group": bill_info["ResourceGroup"],
                "instance_tag": bill_info["Tag"],
                "instance_config": bill_info["InstanceConfig"],
                "instance_spec": bill_info["InstanceSpec"],
                "internet_ip": bill_info["InternetIP"],
                "intranet_ip": bill_info["IntranetIP"],
                "region": bill_info["Region"],
                "zone": bill_info["Zone"],
                "pretax_gross_amount": bill_info["PretaxGrossAmount"],
                "invoice_discount": bill_info["InvoiceDiscount"],
                "deducted_by_coupons": bill_info["DeductedByCoupons"],
                "pretax_amount": bill_info["PretaxAmount"],
                "payment_amount": bill_info["PaymentAmount"],
                "deducted_by_cash_coupons": bill_info["DeductedByCashCoupons"],
                "deducted_by_prepaid_card": bill_info["DeductedByPrepaidCard"],
                "outstanding_amount": bill_info["OutstandingAmount"],
                "currency": bill_info["Currency"],
            }

            # 过滤短信费用
            if data_tmp["account_name"] == "welabconsfin" and data_tmp["product_name"] == "短信服务":
                logger.info(f"account_name:welabconsfin product_name:短信服务 => {data_tmp['pretax_amount']}")
                continue
            
            # 过滤支持计划，因为这个没三个月会使用代金券来购买的，影响公共费用的展示
            if data_tmp["account_name"] == "welabconsfin" and data_tmp["product_name"] == "支持计划":
                logger.info(f"account_name:welabconsfin product_name:支持计划 => {data_tmp['pretax_amount']}")
                continue

            data.append(data_tmp)

        return data


    def down_bill_to_excel(self, cycle):
        """
        下载公司账单到excel文件
        """
        bill_infos_tmp = self.get_bill_by_company(cycle)
        bill_infos = []
        SubscriptionType = {
            "PayAsYouGo": "后付费",
            "Subscription": "预付费",
        }
        Item = {
            "Refund": "退款",
            "PayAsYouGoBill": "后付费账单",
            "SubscriptionOrder": "预付账单",
            "Adjustment": "调账"
        }
        for bill_tmp in bill_infos_tmp:
            bill_infos.append({
                "账期": cycle,
                "财务单元": bill_tmp["CostUnit"],
                "账号ID": bill_tmp["OwnerID"],
                "Owner账号ID": bill_tmp["OwnerID"],
                "产品Code": bill_tmp["ProductCode"],
                "产品": bill_tmp["ProductName"],
                "产品明细Code": bill_tmp["CommodityCode"],
                "产品明细": bill_tmp["ProductDetail"],
                "消费类型": SubscriptionType[bill_tmp["SubscriptionType"]],
                "服务时长": bill_tmp["ServicePeriod"],
                "时长单位": bill_tmp["ServicePeriodUnit"],
                "账单类型": Item[bill_tmp["Item"]],
                "计费方式": bill_tmp["BillingType"],
                "计费项": bill_tmp["BillingItem"],
                "实例ID": bill_tmp["InstanceID"],
                "实例昵称": bill_tmp["NickName"],
                "资源组": bill_tmp["ResourceGroup"],
                "实例标签": bill_tmp["Tag"],
                "实例配置": bill_tmp["InstanceConfig"],
                "实例规格": bill_tmp["InstanceSpec"],
                "公网IP": bill_tmp["InternetIP"],
                "私网IP": bill_tmp["IntranetIP"],
                "地域": bill_tmp["Region"],
                "可用区": bill_tmp["Zone"],
                "原价": bill_tmp["PretaxGrossAmount"],
                "优惠金额": bill_tmp["InvoiceDiscount"],
                "优惠券抵扣": bill_tmp["DeductedByCoupons"],
                "应付金额": bill_tmp["PretaxAmount"],
                "现金支付": bill_tmp["PaymentAmount"],
                "代金券抵扣": bill_tmp["DeductedByCashCoupons"],
                "储值卡支付金额": bill_tmp["DeductedByPrepaidCard"],
                "欠费金额": bill_tmp["OutstandingAmount"],
                "币种": bill_tmp["Currency"],
            })

        df = pd.DataFrame(bill_infos)
        df.to_excel(f"{cycle}-{self.company_name}-账单.xlsx",index=False)
        return True



    def count_account_by_pd(self, billing_cycle):
        """
        直接统计阿里云的账单信息，方便实时看到当月的费用
        """
        bill_info = self.get_bill_by_company(billing_cycle)
        for bill in bill_info:
            if bill["BillAccountName"] == "welabconsfin" and bill["ProductDetail"] == "系统短信":
                bill_info.remove(bill)
                print(f"系统短信费用： {bill['PretaxAmount']}")
                break

        df = pd.DataFrame(bill_info)

        df["account_name"] = df["OwnerID"].map(self.company_account_info[self.company_name])
        df_data = df.groupby(by=["account_name"]).agg({'PretaxAmount':'sum'}).reset_index()
        total = round(df_data["PretaxAmount"].sum(),2)

        result = df_data.to_dict(orient="records")

        return total, result


    def count_account_amount(self, billing_cycle):
        """
        实时下载阿里云的账单信息
        """
        bill_info = self.get_bill_by_company(billing_cycle)
        for bill in bill_info:
            if bill["BillAccountName"] == "welabconsfin" and bill["ProductDetail"] == "系统短信":
                bill_info.remove(bill)
                print(f"系统短信费用： {bill['PretaxAmount']}")
                break

        df = pd.DataFrame(bill_info)

        df["account_name"] = df["OwnerID"].map(self.company_account_info[self.company_name])
        df_data = df.groupby(by=["account_name"]).agg({'PretaxAmount':'sum'}).reset_index()
        print(df_data)

        excel_writer = pd.ExcelWriter(f"{self.company_name}年度账号费用.xlsx", mode='w')
        df_data.to_excel(excel_writer, sheet_name=f"{billing_cycle}", index=False)
        df.to_excel(excel_writer, sheet_name=f"{billing_cycle}详细账单", index=False)
        excel_writer.close()

        sum_amount = df_data["PretaxAmount"].sum()
        print(f"{self.company_name} => {round(sum_amount, 2)}")

        return sum_amount




def main():
    welab_bill = AliyunBillUtils("welab", '', '', 'cn-shenzhen')
    tianmian_bill = AliyunBillUtils("tianmian", '', '', 'cn-shenzhen')
    # bill_info = aliyun_bill.get_bill_by_account("2021-02", 1480048852793114)
    # # pprint(bill_info)
    # df = pd.DataFrame(bill_info)
    # # print(df)
    # df.to_excel("账单.xlsx")

    # 导出账单到excel文件
    # aliyun_bill.down_bill_by_company("2021-01", "welab")

    # # 处理账单类型
    # bill_info = welab_bill.get_bill_by_company("2021-01")
    # welab_bill.handle_payment_type(bill_info)

    # 统计每个账号的金额
    dates = [
        # "2021-09",
        "2021-12"
    ]
    for date in dates:
        welab_sum = welab_bill.count_account_amount(date)
        tianmian_sum = tianmian_bill.count_account_amount(date)
        print(f"{date}总共费用: {round(welab_sum + tianmian_sum,2)}")

    # 统计每个产品的金额
    # welab_bill.statistic_product_amount("2021-03")
    # tianmian_bill.statistic_product_amount("2021-03")

    #测试
    # welab_bill.get_bill_to_database("2021-04")



if __name__ == "__main__":
    main()
